import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import warnings
import openpyxl
from openpyxl import load_workbook

import xlrd
import xlwt
import re

要货计划表路径='../文件/裕华要货计划FCST6.11.xlsx'
客户料号对应SAP料号表='../文件/客户料号与SAP料号对应表.xlsx'
料号对应海外采购表路径='../文件/海外采购配置表.xlsx'

def readNmuber():

    # 获取要货计划表中的客户料号和对应时间数据,获取对应的SAP料号
    readPlanDate = pd.read_excel(要货计划表路径, dtype=str)
    PlanDate1 = readPlanDate[['机型', '物料编码', '物料描述']]
    # 利用正则表达式获取到表头为时间类型的列
    date_pattern = r'\d{4}-\d{2}-\d{2}'
    column_names = readPlanDate.columns[
        readPlanDate.columns.str.contains(date_pattern, case=False, na=False, regex=True)]
    PlanDate2 = readPlanDate[column_names]
    # 将两个表数据进行合并
    concatDate = pd.concat([PlanDate1, PlanDate2], axis=1)
    Log.Info(self, f'获取到要货计划表中{len(concatDate)}条数据')

    # 读取成品料号对应表sheet页的数据
    readSAPNumber = pd.read_excel(客户料号对应SAP料号表, sheet_name='成品料号对应表', dtype=str)
    readPlanDate.rename(columns={'物料编码': '客户料号'}, inplace=True)

    # 拼接两个表,得到客户料号对应的SAP料号
    mergeDate = pd.merge(readPlanDate['客户料号'], readSAPNumber, how='left', on=['客户料号'], indicator=True)
    resultDate = mergeDate[mergeDate['_merge'].isin(['both'])].drop(columns=['_merge'])
    Log.Info(self, f'能够获取到客户料号对应SAP料号的有{len(resultDate)}条数据')
    Log.Info(self,resultDate)
    # 将SAP料号数据转换为列表
    # SAPNmuberList = resultDate['SAP料号'].values.tolist()
    # Log.Info(self, f'将SAP料号转换为列表:{SAPNmuberList}')

    #获取海外物料采购表中的料号数据
    readOverseasDate=pd.read_excel(料号对应海外采购表路径,dtype=str,sheet_name='海外物料采购表')
    Log.Info(self,readOverseasDate)
    pd.merge()

# readNmuber()
def readNmuber1():
    warnings.filterwarnings('ignore')
    pd.set_option('display.max_columns', None)
    # 获取要货计划表中的客户料号和对应时间数据,获取对应的SAP料号
    readPlanDate = pd.read_excel(要货计划表路径)
    PlanDate1 = readPlanDate[['机型', '物料编码', '物料描述']]
    # 利用正则表达式获取到表头为时间类型的列
    date_pattern = r'\d{4}-\d{2}-\d{2}'
    column_names = readPlanDate.columns[
        readPlanDate.columns.str.contains(date_pattern, case=False, na=False, regex=True)]
    timecolumn = column_names.values.tolist()

    # 将两个表数据进行合并
    PlanDate2 = readPlanDate[column_names]
    PlanDate2.fillna(0, inplace=True)
    for column in timecolumn:
        PlanDate2[column].astype(float)
    concatDate = pd.concat([PlanDate1, PlanDate2], axis=1)
    # Log.Info(self,concatDate)
    Log.Info(self,f'获取到要货计划表中{len(concatDate)}条数据')
    concatDate.rename(columns={'物料编码': '客户料号'}, inplace=True)

    # 读取成品料号对应表sheet页的数据
    readSAPNumber = pd.read_excel(料号对应海外采购表路径, sheet_name='成品料号对应表', dtype=str)

    # 拼接两个表,得到客户料号对应的SAP料号
    mergeDate = pd.merge(readSAPNumber[['SAP料号', '客户料号', '开单周期']], concatDate, how='right', on=['客户料号'],
                         indicator=True)
    mergeresult = mergeDate[mergeDate['_merge'].isin(['both'])].drop(columns=['_merge'])
    mergeresult.rename(columns={'SAP料号':'成品SAP料号'},inplace=True)
    # Log.Info(self,mergeresult)

    # 获取海外物料采购表中的料号数据
    readOverseasDate = pd.read_excel(料号对应海外采购表路径, dtype=str, sheet_name='海外物料采购表')

    # Log.Info(self,readOverseasDate)

    resultDate=pd.merge(readOverseasDate,mergeresult,on=['成品SAP料号'],how='left')
    Log.Info(self,resultDate)
    # resultDate.fillna(0, inplace=True)
    sums = []

    # 遍历表中的每一行数据
    for index, row in resultDate.iterrows():
        Log.Info(self,row['成品SAP料号'])
        if(str(row['成品SAP料号'])=='nan'):
            sums.append(0)
            # Log.Info(self,'继续')
            continue
        nowday = datetime.now().date()
        # 使用正则表达式查找连续的数字
        match = re.search(r'\d+', row['交期'])
        # 如果找到了匹配项
        if match:
            # 获取匹配到的数字字符串
            times = int(match.group())
        else:
            times = 0
            Log.Info(self,"没有找到数字")
        timeList = []
        # 从今天nowday开始,循环times次,把每天的数据相加起来
        for i in range(0, times):
            today = nowday.strftime('%Y-%m-%d')
            if (today in timecolumn):
                timeList.append(today)
            nowday += timedelta(days=1)
        sum_value = row[timeList].sum()
        sums.append(sum_value)
    # 定义'需求'列的数据
    resultDate['需求'] = sums
    #获取只需要的表头数据
    headList=['成品SAP料号', '物料SAP料号','物料短描述','交期','用量','库存','成品数量','材料数量','差异','客户料号','机型','物料描述','需求']
    returnDate=resultDate[headList]
    Log.Info(self,returnDate)

readNmuber1()